package com.dw.etl.core.service;

import com.dw.etl.common.base.mapper.BaseMapper;
import com.dw.etl.common.util.SnowflakeIdWorker;
import com.dw.etl.common.util.UUIDUtil;
import com.dw.etl.common.vo.PaginationVO;
import com.dw.etl.core.api.*;
import com.dw.etl.core.bean.model.*;
import com.dw.etl.core.bean.vo.*;
import com.dw.etl.core.config.EtlConstant;
import com.dw.etl.core.mapper.EtlIndexsqlDimfldMapper;
import com.dw.etl.core.mapper.EtlIndexsqlDwtbMapper;
import com.dw.etl.core.mapper.EtlIndexsqlMapper;
import com.dw.etl.core.utils.EtlUtil;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;

/**
 * etlIndexsql服务实现类
 * Created by hozeData on 2021/07/13.
 */
@Service
@Transactional
public class EtlIndexsqlServiceImpl implements EtlIndexsqlService{

    private static final Logger LOGGER = LoggerFactory.getLogger(EtlIndexsqlServiceImpl.class);

    @Autowired
    private EtlIndexsqlMapper etlIndexsqlMapper;
    @Autowired
    EtlIndexsqlDimfldMapper etlIndexsqlDimfldMapper;
    @Autowired
    EtlIndexsqlDwtbMapper etlIndexsqlDwtbMapper;
    @Autowired
    MdMedTbService mdMedTbService;
    @Autowired
    MdMedTbFldService mdMedTbFldService;
    @Autowired
    MdMedDmtbService mdMedDmtbService;
    @Autowired
    MdMedDimService mdMedDimService;

    @Value("${etl.main.datasource.type}")
    private String dbType;

    @Override
    public int insert(EtlIndexsql etlIndexsql) {
        if(StringUtils.isEmpty(etlIndexsql.getIdIndexsql())){
            String uuid32 = UUIDUtil.getUUID32();
            etlIndexsql.setIdIndexsql(uuid32);
        }
        return etlIndexsqlMapper.insert(etlIndexsql);
    }

    @Override
    public int insertSelective(EtlIndexsql etlIndexsql)  {
        if(StringUtils.isEmpty(etlIndexsql.getIdIndexsql())){
            String uuid32 = UUIDUtil.getUUID32();
            etlIndexsql.setIdIndexsql(uuid32);
        }
        return etlIndexsqlMapper.insertSelective(etlIndexsql);
    }

    @Override
    public int deleteById(String idIndexsql)  {
        return etlIndexsqlMapper.deleteByPrimaryKey(idIndexsql);
    }

    @Override
    public int updateByIdSelective(EtlIndexsql etlIndexsql)  {

        return etlIndexsqlMapper.updateByPrimaryKeySelective(etlIndexsql);
    }

    @Override
    public int updateById(EtlIndexsql etlIndexsql)  {
        return etlIndexsqlMapper.updateByPrimaryKey(etlIndexsql);
    }

    @Override
    public EtlIndexsql getById(String idIndexsql)  {
        return etlIndexsqlMapper.selectByPrimaryKey(idIndexsql);
    }

    @Override
    public List<EtlIndexsql> findByModel(EtlIndexsql etlIndexsql)  {
        return etlIndexsqlMapper.findByModel(etlIndexsql);
    }

    @Override
    public PaginationVO<EtlIndexsql> findPageByModel(EtlIndexsql etlIndexsql, Integer pageNum, Integer pageSize) {
        PageHelper.startPage(pageNum == null? 1 : pageNum, pageSize == null? 10 : pageSize);
        List<EtlIndexsql> quesList = etlIndexsqlMapper.findByModel(etlIndexsql);
        PageInfo<EtlIndexsql> pageInfo = new PageInfo<>(quesList);
        PaginationVO<EtlIndexsql> paginationVo = new PaginationVO(pageInfo.getTotal(), pageInfo.getList());
        return paginationVo;
    }

    @Override
    public EtlIndexShowVO getByIndex(String idIndex) {
        EtlIndexsql indexsql = new EtlIndexsql();
        indexsql.setIdIndex(idIndex);
        List<EtlIndexsql> confList = this.findByModel(indexsql);
        EtlIndexShowVO showVO = new EtlIndexShowVO();
        if(confList.size()==1) {
            indexsql = confList.get(0);
            EtlIndexsqlDwtb dwtb = new EtlIndexsqlDwtb();
            dwtb.setIdIndexsql(indexsql.getIdIndexsql());
            List<EtlIndexsqlDwtb> dwtbList = etlIndexsqlDwtbMapper.findByModel(dwtb);
            EtlIndexsqlDimfld dimfld = new EtlIndexsqlDimfld();
            dimfld.setIdIndexsql(indexsql.getIdIndexsql());
            List<EtlIndexsqlDimfld> dimfldList = etlIndexsqlDimfldMapper.findByModel(dimfld);
            showVO.setEtlIndexsql(indexsql);
            Map<String,List<EtlTbFldAttrVO>> tbFldsMap = new HashMap<>();
            List<MdMedTbFld> mdMedTbFldList;
            MdMedTb mdMedTb;
            EtlTbFldAttrVO fldAttrVO;
            List<EtlTbFldAttrVO> fldAttrVOList;
            String sqlOn = "";
            for(EtlIndexsqlDwtb tb : dwtbList) {
                mdMedTb = mdMedTbService.getById(tb.getIdTb());
                mdMedTbFldList = mdMedTbFldService.findValidTbField(tb.getIdTb());
                fldAttrVOList = new ArrayList<>();
                if(StringUtils.isNotEmpty(tb.getSqlJoin())) {
                    sqlOn += tb.getSqlJoin()+";";
                }
                for(MdMedTbFld tbFld:mdMedTbFldList) {
                    fldAttrVO = new EtlTbFldAttrVO();
                    fldAttrVO.setIdPubfld(tbFld.getIdPubfld());
                    fldAttrVO.setNa(tbFld.getNa());
                    fldAttrVO.setTbName(tb.getIdTb());
                    for(EtlIndexsqlDimfld fld:dimfldList) {
                        if(fld.getIdPubfld().equals(tbFld.getIdPubfld()) && fld.getIdTb().equals(tbFld.getIdTb())) {
                            fldAttrVO.setFgCheck(1);
                            break;
                        }
                    }
                    fldAttrVOList.add(fldAttrVO);
                }
                tbFldsMap.put(mdMedTb.getIdTb()+"+++"+mdMedTb.getNa(),fldAttrVOList);
            }
            showVO.setEtlIndexsqlDimfldList(dimfldList);
            showVO.setSqlOn(sqlOn);
            showVO.setTbFldsMap(tbFldsMap);
        } else {
            /**
             * 报错提醒
             */
        }
        return showVO;
    }

    @Override
    public LayMsg save(EtlIndexSqlVO etlIndexSqlVO) {
        EtlIndexsql etlIndexsql = etlIndexSqlVO.getEtlIndexsql();
        List<EtlIndexsqlDimfld> dimfldList = etlIndexSqlVO.getIndexsqlDimfldList();
        String sqlOn = etlIndexSqlVO.getSqlOn();
        EtlIndexsqlDwtb dwtb;
        String mainTable = etlIndexSqlVO.getMainTable();
        String idIndexsql = etlIndexsql.getIdIndexsql();
        if(StringUtils.isEmpty(idIndexsql)) {
            idIndexsql = UUIDUtil.getUUID32();
            etlIndexsql.setIdIndexsql(idIndexsql);
        } else {
            deleteById(idIndexsql);
            etlIndexsqlDimfldMapper.deleteByIdIndexsql(idIndexsql);
            etlIndexsqlDwtbMapper.deleteByIdIndexsql(idIndexsql);
        }
        this.insert(etlIndexsql);
        if(dimfldList!=null) {
            for(EtlIndexsqlDimfld fld : dimfldList) {
                fld.setIdIndexsqlDimfld(UUIDUtil.getUUID32());
                fld.setIdIndexsql(idIndexsql);
                etlIndexsqlDimfldMapper.insert(fld);
            }
        }
        List<String> dwTableList = new ArrayList<>();
        if(sqlOn!=null) {
            String[] sqlJoins = sqlOn.split(";");
            String pkTb;
            for(String sqlJoin:sqlJoins) {
                pkTb = EtlUtil.extrCharFromString(sqlJoin,"[[","]]");
                if(!dwTableList.contains(pkTb) && pkTb!=null) {
                    dwtb = new EtlIndexsqlDwtb();
                    dwtb.setIdIndexsql(idIndexsql);
                    dwtb.setIdIndexsqlDwtb(UUIDUtil.getUUID32());
                    dwtb.setSqlJoin(sqlOn);
                    dwtb.setIdTb(pkTb);
                    etlIndexsqlDwtbMapper.insert(dwtb);
                    dwTableList.add(pkTb);
                }
            }
        }
        if(StringUtils.isNotEmpty(mainTable)) {
            if(!dwTableList.contains(mainTable)) {
                dwTableList.add(mainTable);
            }
            dwtb = new EtlIndexsqlDwtb();
            dwtb.setIdIndexsql(idIndexsql);
            dwtb.setIdTb(mainTable);
            dwtb.setIdIndexsqlDwtb(UUIDUtil.getUUID32());
            etlIndexsqlDwtbMapper.insert(dwtb);
        }
        return createDmTable(dwTableList,etlIndexsql);
    }



    private LayMsg createDmTable(List<String> pkTbList,EtlIndexsql indexsql){
        MdMedTbFld tbFdModel = new MdMedTbFld();
        MdMedTbFld cloneFld;
        tbFdModel.setFgAct((byte)1);
        List<MdMedTbFld> tbFdList;
        Set<String> dimFldSet = new HashSet<>();
        String idPubfld;
        List<MdMedTbFld> medTbFldList = new ArrayList<>();
        for(String pkTb:pkTbList) {
            tbFdModel.setIdTb(pkTb);
            tbFdList = mdMedTbFldService.findByModel(tbFdModel);
            for(MdMedTbFld fld:tbFdList) {
                idPubfld = fld.getIdPubfld();
                if(idPubfld.startsWith("id_dim_") && !idPubfld.startsWith("id_dim_day_") && !idPubfld.startsWith("id_dim_time_")) {
                    if(!dimFldSet.contains(idPubfld)) {
                        dimFldSet.add(idPubfld);
                        medTbFldList.add(fld);
                    }
                }
            }
        }
        LayMsg layMsg;
        if(medTbFldList.size()>0) {
            MdMedDmtb dmtb = new MdMedDmtb();
            dmtb.setIdIndex(indexsql.getIdIndex());
            dmtb.setCdDimgrp(EtlConstant.CD_DIM_GROUP_DEF);
            layMsg = mdMedDmtbService.createDmTable(medTbFldList,dmtb);
        } else {
            layMsg = LayMsg.fail().addMsg("维度字段为空，不支持自动建表");
        }
        return layMsg;
    }

    /**
     * 样例sql
     * select 维度集,指标,dimDate from 主表
     * inner join 从表1 on 主表.字段=从表1.字段
     * ...
     * inner join 维表1 on 主(从)表.字段=维表1.字段
     * where 条件
     * group by 维度集,dimDate
     *
     * 获取全部维度集去重，记录主表与join表（事实表、维度表）、where条件
     * @return
     */
    @Override
    public List<DmSqlVO> findAllSql() {
        List<EtlIndexsql> indexsqlList = this.findByModel(new EtlIndexsql());
        List<EtlIndexsqlDwtb> dwtbList;
        List<EtlIndexsqlDimfld> dimfldList;
        EtlIndexsqlDwtb dwtb,dwtbModel = new EtlIndexsqlDwtb();
        EtlIndexsqlDimfld dimFld,dimFldModel = new EtlIndexsqlDimfld();
        MdMedTbFld tbFldModel = new MdMedTbFld(),tbFld;MdMedDmtb medDmtb;
        String idIndexsql;MdMedDim mdMedDim;
        List<MdMedTbFld> dmTbFldList,dwTbFldList;
        Iterator<MdMedTbFld> fldIterator;
        List<String> idPubfldList;
        List<DmSqlVO> returnDataList = new ArrayList<>();
        DmSqlVO dmSqlVO;
        for(EtlIndexsql indexsql:indexsqlList) {
            dmSqlVO = new DmSqlVO();
            idIndexsql = indexsql.getIdIndexsql();
            dwtbModel.setIdIndexsql(idIndexsql);
            dwtbList = etlIndexsqlDwtbMapper.findByModel(dwtbModel);
            dimFldModel.setIdIndexsql(idIndexsql);
            dimfldList = etlIndexsqlDimfldMapper.findByModel(dimFldModel);
            String dimFields = "",mainTable = "",joinTable = "",sql="";
            if(dwtbList.size()==0) {
                continue;
            }
            medDmtb = mdMedDmtbService.getTbByIndex(indexsql.getIdIndex());
            if(medDmtb==null) {
                continue;
            }
            dmTbFldList = mdMedTbFldService.findTbDimField(medDmtb.getIdTb());
            if(dmTbFldList.size()==0) {
                continue;
            }
            if(dwtbList.size()==1 && dimfldList.size()==0) { //单表无需给表起别名，也无join on操作。
                dwtb = dwtbList.get(0);
                for(MdMedTbFld fld: dmTbFldList) {
                    dimFields += fld.getIdPubfld()+",";
                }
                mainTable = dwtb.getIdTb();
            } else { //多张事实表，考虑级联性
                if(dimfldList.size()>0) {
                    for(EtlIndexsqlDimfld fld:dimfldList) {
                        mdMedDim = mdMedDimService.getByIdFromCache(fld.getIdPubfld());
                        joinTable = " inner join "+fld.getIdTb()+" on "+fld.getIdTb()+"."+fld.getIdPubfld()+"="+mdMedDim.getTbDim()+".id";
                    }
                }
                if(dwtbList.size()>0) {
                    fldIterator = dmTbFldList.iterator();
                    for(EtlIndexsqlDwtb tb:dwtbList) {
                        if(StringUtils.isEmpty(tb.getSqlJoin())) {
                            mainTable = tb.getIdTb();
                        }else {
                            joinTable = " inner join "+tb.getIdTb()+" on "+tb.getSqlJoin();
                        }
                        dwTbFldList = mdMedTbFldService.findTbDimField(medDmtb.getIdTb());
                        idPubfldList = new ArrayList<>();
                        for(MdMedTbFld fld: dwTbFldList) {
                            idPubfldList.add(fld.getIdPubfld());
                        }
                        while (fldIterator.hasNext()) {
                            tbFld = fldIterator.next();
                            if(idPubfldList.contains(tbFld.getIdPubfld())) {
                                dimFields += tb.getIdTb()+"."+tbFld.getIdPubfld()+",";
                                fldIterator.remove();
                            }
                        }
                    }
                }
            }
            if(StringUtils.isNotEmpty(mainTable)) {
                sql = "select "+dimFields+" '"+indexsql.getIdIndex()+"' as id_index,'"+indexsql.getNaIndex()+"' as na_index, "+indexsql.getSqlVal()+" as value, "+ indexsql.getGrpDtfld()+" as dim_day_occur from "
                        + mainTable+joinTable+" where id_dim_org='{"+ EtlConstant.ID_DIM_ORG+"}' and "+indexsql.getGrpDtfld()+"='{"+ EtlConstant.DIM_DAY_BEGIN+"}' "
                        +(indexsql.getSqlWhere()==null?"":" and "+indexsql.getSqlWhere())
                        +" group by "+dimFields+" "+indexsql.getGrpDtfld();
                dmSqlVO.setQrySql(sql);
                dmSqlVO.setDelSql("delete from "+medDmtb.getIdTb()+" where "+indexsql.getGrpDtfld()+"='{"+ EtlConstant.DIM_DAY_BEGIN+"}'");
                dmSqlVO.setTarTableName(medDmtb.getIdTb());
                returnDataList.add(dmSqlVO);
            }
        }
        return returnDataList;
    }
}
